package csc.fresher.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import javax.sql.DataSource;

import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import csc.fresher.commons.DateUtils;
import csc.fresher.dao.AccountHasInterestRateDao;
import csc.fresher.dao.PeriodTypeDao;
import csc.fresher.dao.SavingAccountDao;
import csc.fresher.entity.AccountHasInterestRate;
import csc.fresher.entity.AccountHasInterestRateState;
import csc.fresher.entity.AccountState;
import csc.fresher.entity.AccountTransactionState;
import csc.fresher.entity.CustomerAccount;
import csc.fresher.entity.InterestRate;
import csc.fresher.entity.PeriodType;
import csc.fresher.entity.SavingAccount;
import csc.fresher.entity.SavingAccountHasInterestRate;
import csc.fresher.entity.SavingTransaction;
import csc.fresher.entity.SavingTransactionType;
import csc.fresher.entity.TransactionState;
import csc.fresher.service.InterestRateService;
import csc.fresher.service.SavingAccountService;
import csc.fresher.service.SavingBookService;
import csc.fresher.utils.DBUtils;
import csc.fresher.utils.DatabaseUtil;

@Repository
public class SavingAccountDaoImpl implements SavingAccountDao {

	@Autowired
	DataSource dataSource;

	@Autowired
	InterestRateService interestRateService;

	@Autowired
	AccountHasInterestRateDao accountHasInterestRateDao;

	@Autowired
	PeriodTypeDao periodTypeDao;

	@Autowired
	SavingAccountService accountService;

	private static final Logger logger = Logger
			.getLogger(SavingAccountDaoImpl.class);

	@Override
	public long insert(SavingAccount savingAccount) {
		String query = "INSERT INTO `SavingAccount` (`AccountType`,`SavingAccountBalance`,`isRepeatable`,`CreatedDate`,`AccountState`, `AccountTransactionState`, `Customer_CustomerID`, `currency_CurrencyID`) VALUES (?,?,?,?,?,?,?,?)";

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = dataSource.getConnection();
			preparedStatement = connection.prepareStatement(query,
					Statement.RETURN_GENERATED_KEYS);
			preparedStatement.setByte(1, savingAccount.getAccountType());
			preparedStatement.setDouble(2, savingAccount.getAccountBalance());
			preparedStatement.setBoolean(3, savingAccount.getRepeatable());
			preparedStatement.setTimestamp(4, savingAccount.getCreatedDate());
			preparedStatement.setString(5, savingAccount.getAccountState()
					.getState());
			preparedStatement.setString(6, savingAccount
					.getAccountTransactionState().getState());
			preparedStatement.setLong(7, savingAccount.getCustomerID());
			preparedStatement.setLong(8, savingAccount.getCurrencyID());
			preparedStatement.execute();
			ResultSet rs = preparedStatement.getGeneratedKeys();
			if (rs.next()) {
				return rs.getLong(1);
			}
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			DBUtils.closeConnection(connection, preparedStatement, logger);
		}
		return 0;
	}

	@Override
	public boolean update(SavingAccount savingAccount) {
		String query = "UPDATE `SavingAccount` "
				+ " SET  `SavingAccount`.`AccountType`=?, `SavingAccount`.`SavingAccountBalance`=?, `SavingAccount`.`isRepeatable`=?, `SavingAccount`.`AccountState`=?, `SavingAccount`.`AccountTransactionState`=?, `SavingAccount`.`Customer_CustomerID`=? "
				+ "WHERE `SavingAccount`.`SavingAccountNumber`=? ";

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = dataSource.getConnection();
			preparedStatement = connection.prepareStatement(query);
			preparedStatement.setByte(1, savingAccount.getAccountType());
			preparedStatement.setDouble(2, savingAccount.getAccountBalance());
			preparedStatement.setBoolean(3, savingAccount.getRepeatable());
			preparedStatement.setString(4, savingAccount.getAccountState()
					.getState());
			preparedStatement.setString(5, savingAccount
					.getAccountTransactionState().getState());
			preparedStatement.setLong(6, savingAccount.getCustomerID());
			preparedStatement.setLong(7, savingAccount.getAccountNumber());

			preparedStatement.execute();
			return true;
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			DBUtils.closeConnection(connection, preparedStatement, logger);
		}
		return false;
	}

	@Override
	public boolean updateRepeatable(SavingAccount savingAccount,
			AccountHasInterestRate accountHasInterestRate, long id, SavingTransaction savingTransaction) {
		String state = "deny";
		String updateQuery = "UPDATE `SavingAccount` "
				+ " SET `SavingAccount`.`SavingAccountBalance`=? "
				+ "WHERE `SavingAccount`.`SavingAccountNumber`=? ";

		String createQuery = "INSERT INTO `SavingAccount_has_InterestRate`(`SavingAccount_AccNumber`,`InterestRate_InterestRateID`,`FromDate`, `ToDate`, `Balance`, `State`) VALUES (?,?,?,?,?,?)";

		String updateQuerySavingBook = "UPDATE `SavingAccount_has_InterestRate` SET `SavingAccount_has_InterestRate`.`State`=? WHERE `SavingAccount_has_InterestRate`.`id` = ?";
		
		String createTransaction = "INSERT INTO `SavingTransaction` (`Funds`, `CreatedDate`, `BeginTiime`,`EndTime`,`SavingTransactionType`,"
				+ "`SavingAccountNumber`, `TransactionState`,`BeforeTxBalance`,"
				+ "`CreatedBy`,`ApprovedBy`) VALUES (?,?,?,?,?,?,?,?,?,?)";

		Connection connection = null;
		PreparedStatement preparedStatementUpdate = null;
		PreparedStatement preparedStatementCreate = null;
		PreparedStatement preparedStatementUpdateSavingBook = null;
		PreparedStatement preparedStatementCreateTransaction = null;

		try {
			connection = dataSource.getConnection();
			connection.setAutoCommit(false);
			preparedStatementUpdate = connection.prepareStatement(updateQuery);
			preparedStatementCreate = connection.prepareStatement(createQuery);
			preparedStatementUpdateSavingBook = connection
					.prepareStatement(updateQuerySavingBook);
			preparedStatementCreateTransaction = connection
					.prepareStatement(createTransaction);

			preparedStatementUpdate.setDouble(1,
					savingAccount.getAccountBalance());
			preparedStatementUpdate
					.setLong(2, savingAccount.getAccountNumber());
			preparedStatementUpdate.execute();

			preparedStatementCreate = connection.prepareStatement(createQuery,
					Statement.RETURN_GENERATED_KEYS);
			preparedStatementCreate.setLong(1,
					accountHasInterestRate.getAccountNumber());
			preparedStatementCreate.setInt(2,
					accountHasInterestRate.getInterestRateID());
			preparedStatementCreate.setTimestamp(3,
					accountHasInterestRate.getFromDate());
			preparedStatementCreate.setTimestamp(4,
					accountHasInterestRate.getToDate());
			preparedStatementCreate.setDouble(5,
					accountHasInterestRate.getBalance());
			preparedStatementCreate.setString(6, accountHasInterestRate
					.getState().getState());
			preparedStatementCreate.execute();

			preparedStatementUpdateSavingBook = connection.prepareStatement(
					updateQuerySavingBook, Statement.RETURN_GENERATED_KEYS);
			preparedStatementUpdateSavingBook.setString(1, state);
			preparedStatementUpdateSavingBook.setLong(2, id);
			preparedStatementUpdateSavingBook.execute();
			
			Timestamp currentTime = new Timestamp(System.currentTimeMillis());
			preparedStatementCreateTransaction.setDouble(1, savingTransaction.getFunds());
			preparedStatementCreateTransaction.setTimestamp(2, currentTime);
			preparedStatementCreateTransaction.setTimestamp(3, currentTime);
			preparedStatementCreateTransaction.setTimestamp(4, currentTime);
			preparedStatementCreateTransaction.setString(5, savingTransaction
					.getSavingTransactionType().getType());
			preparedStatementCreateTransaction.setLong(6,
					savingTransaction.getCustomerAccountNumber());
			preparedStatementCreateTransaction.setString(7, savingTransaction.getTransactionState().getState());
			preparedStatementCreateTransaction.setDouble(8,
					savingTransaction.getBeforeTxBalance());
			preparedStatementCreateTransaction.setLong(9, savingTransaction.getCreatedBy());
			preparedStatementCreateTransaction.setLong(10, savingTransaction.getVerifiedBy());

			preparedStatementCreateTransaction.execute();

			connection.commit();
			
			return true;
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
			if (connection != null) {
				try {
					System.err.print("Transaction is being rolled back");
					connection.rollback();
				} catch (SQLException excep) {
					excep.printStackTrace();
				}
			}
		} finally {
			try {
				connection.setAutoCommit(false);
			} catch (SQLException e) {
				e.printStackTrace();
			}
			DBUtils.closeConnection(connection, preparedStatementUpdate, logger);
			DBUtils.closeConnection(connection, preparedStatementCreate, logger);
		}
		return false;
	}

	@Override
	public boolean changeRateBeforeExpiredDate(SavingAccount savingAccount,
			AccountHasInterestRate oldRate, AccountHasInterestRate newRate) {
		String query = "UPDATE `SavingAccount` "
				+ " SET  `SavingAccount`.`SavingAccountBalance`=?, `SavingAccount`.`AccountTransactionState`=?"
				+ " WHERE `SavingAccount`.`SavingAccountNumber`=? ";
		String query2 = "UPDATE `SavingAccount_has_InterestRate` SET `State` = ?   WHERE `SavingAccount_has_InterestRate`.`id` = ? ";
		String query3 = "INSERT INTO `SavingAccount_has_InterestRate`(`SavingAccount_AccNumber`,`InterestRate_InterestRateID`,`FromDate`, `ToDate`, `Balance`, `State`) VALUES (?,?,?,?,?,?)";
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		PreparedStatement preparedStatement2 = null;
		PreparedStatement preparedStatement3 = null;
		try {
			connection = dataSource.getConnection();
			connection.setAutoCommit(false);
			preparedStatement = connection.prepareStatement(query);
			preparedStatement.setDouble(1, savingAccount.getAccountBalance());
			preparedStatement.setString(2, savingAccount
					.getAccountTransactionState().getState());
			preparedStatement.setLong(3, savingAccount.getAccountNumber());
			if (preparedStatement.executeUpdate() == 0) {
				connection.rollback();
				return false;
			}

			preparedStatement2 = connection.prepareStatement(query2);
			preparedStatement2.setString(1, oldRate.getState().getState());
			preparedStatement2.setLong(2, oldRate.getId());
			if (preparedStatement2.executeUpdate() == 0) {
				connection.rollback();
				return false;
			}

			long newID = 0;
			preparedStatement3 = connection.prepareStatement(query3,
					Statement.RETURN_GENERATED_KEYS);
			preparedStatement3.setLong(1, newRate.getAccountNumber());
			preparedStatement3.setInt(2, newRate.getInterestRateID());
			preparedStatement3.setTimestamp(3, newRate.getFromDate());
			preparedStatement3.setTimestamp(4, newRate.getToDate());
			preparedStatement3.setDouble(5, newRate.getBalance());
			preparedStatement3.setString(6, newRate.getState().getState());
			preparedStatement3.execute();
			ResultSet rs = preparedStatement3.getGeneratedKeys();
			if (rs.next()) {
				newID = rs.getLong(1);
			}

			if (newID <= 0) {
				connection.rollback();
				return false;
			}

			connection.commit();
			return true;
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			DBUtils.closeConnection(connection, preparedStatement, logger);
		}
		return false;
	}

	@Override
	public boolean delete(SavingAccount savingAccount) {
		return false;

	}

	/*
	 * @Override public CustomerAccount getCustomerAccountByID(long
	 * cutomerAccountID) { String query =
	 * "SELECT * FROM `SavingAccount` WHERE `SavingAccount`.`SavingAccountNumber`=?"
	 * ; SavingAccount savingAccount = null; Connection connection = null;
	 * PreparedStatement preparedStatement = null; ResultSet rs = null; try {
	 * connection = dataSource.getConnection(); preparedStatement =
	 * connection.prepareStatement(query); preparedStatement.setLong(1,
	 * cutomerAccountID); rs = preparedStatement.executeQuery(); if(rs.next())
	 * savingAccount = getSavingAccountResultSet(rs); } catch (SQLException e) {
	 * logger.error(e); e.printStackTrace(); } finally {
	 * DBUtils.closeResultSetConnection(connection, preparedStatement, rs,
	 * logger); } return savingAccount; }
	 */

	@Override
	public CustomerAccount getSavingAccountByCustomerID(long cutomerAccountID) {
		String query = "SELECT * FROM `SavingAccount` WHERE `SavingAccount`.`SavingAccountNumber`=?";
		SavingAccount savingAccount = null;
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet rs = null;
		try {
			connection = dataSource.getConnection();
			preparedStatement = connection.prepareStatement(query);
			preparedStatement.setLong(1, cutomerAccountID);
			rs = preparedStatement.executeQuery();
			if (rs.next())
				savingAccount = getSavingAccountResultSet(rs);
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			DBUtils.closeResultSetConnection(connection, preparedStatement, rs,
					logger);
		}
		return savingAccount;
	}

	/*
	 * @Override public CustomerAccount getCustomerAccountByCardNumberID(long
	 * cardNumberID) { String query =
	 * "SELECT * FROM `savingaccount` LEFT JOIN `customer`" +
	 * " ON `savingaccount`.`Customer_CustomerID` = `customer`.`CustomerID`" +
	 * " WHERE `customer`.`IDCardNumber`= ?" +
	 * " ORDER BY `savingaccount`.`CreatedDate` DESC "; SavingAccount
	 * savingAccount = null; Connection connection = null; PreparedStatement
	 * preparedStatement = null; ResultSet rs = null; try { connection =
	 * dataSource.getConnection(); preparedStatement =
	 * connection.prepareStatement(query); preparedStatement.setLong(1,
	 * cardNumberID); rs = preparedStatement.executeQuery(); if(rs.next())
	 * savingAccount = getSavingAccountResultSet(rs); } catch (SQLException e) {
	 * logger.error(e); e.printStackTrace(); } finally {
	 * DBUtils.closeResultSetConnection(connection, preparedStatement, rs,
	 * logger); } return savingAccount; }
	 */

	@Override
	public SavingAccount getCustomerAccountByCardNumberID(long cardNumberID) {
		String query = "SELECT * FROM `savingaccount` LEFT JOIN `customer`"
				+ " ON `savingaccount`.`Customer_CustomerID` = `customer`.`CustomerID`"
				+ " WHERE `customer`.`IDCardNumber`= ?"
				+ " ORDER BY `savingaccount`.`CreatedDate` DESC ";
		SavingAccount savingAccount = null;
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet rs = null;
		try {
			connection = dataSource.getConnection();
			preparedStatement = connection.prepareStatement(query);
			preparedStatement.setLong(1, cardNumberID);
			rs = preparedStatement.executeQuery();
			if (rs.next())
				savingAccount = getSavingAccountResultSet(rs);
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			DBUtils.closeResultSetConnection(connection, preparedStatement, rs,
					logger);
		}
		return savingAccount;
	}

	private SavingAccount getSavingAccountResultSet(ResultSet rs)
			throws SQLException {
		SavingAccount savingAccount = new SavingAccount();
		savingAccount.setAccountNumber(rs.getLong("SavingAccountNumber"));
		savingAccount.setAccountType(rs.getByte("AccountType"));
		savingAccount.setAccountBalance(rs.getDouble("SavingAccountBalance"));
		savingAccount.setRepeatable(rs.getBoolean("isRepeatable"));
		savingAccount.setCreatedDate(rs.getTimestamp("CreatedDate"));
		savingAccount.setAccountState(AccountState.valueOf(rs.getString(
				"AccountState").toUpperCase()));
		savingAccount
				.setAccountTransactionState(AccountTransactionState.valueOf(rs
						.getString("AccountTransactionState").toUpperCase()));
		savingAccount.setCustomerID(rs.getLong("Customer_CustomerID"));
		savingAccount.setCurrencyID(rs.getInt("currency_CurrencyID"));
		return savingAccount;
	}

	/*
	 * @Override public List<CustomerAccount>
	 * getListCutomerAccountsbyCardNumberID( long cardNumberID) { // TODO
	 * Auto-generated method stub return null; }
	 */

	@Override
	public List<CustomerAccount> getListCutomerAccountsbyCardNumberID(
			long cardNumberID) {
		List<CustomerAccount> customerAccounts = new ArrayList<CustomerAccount>();
		String query = "SELECT * FROM `savingaccount` LEFT JOIN `customer`"
				+ " ON `savingaccount`.`Customer_CustomerID` = `customer`.`CustomerID`"
				+ " WHERE `customer`.`IDCardNumber`= ?"
				+ " ORDER BY `savingaccount`.`CreatedDate` DESC";
		SavingAccount savingAccount = null;
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet rs = null;
		try {
			connection = dataSource.getConnection();
			rs = DatabaseUtil.executeQuery(preparedStatement, connection,
					query, new Object[] { cardNumberID });
			if (rs != null) {
				while (rs.next()) {
					savingAccount = getSavingAccountResultSet(rs);
					customerAccounts.add(savingAccount);
				}
			}
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			DatabaseUtil.closeConnection(connection, preparedStatement, logger);
		}
		return customerAccounts;
	}

	@Override
	public boolean checkActiveCustomerAccountTransactionState(
			long customerAccountID) {
		String query = "SELECT `AccountTransactionState` FROM `SavingAccount` WHERE `SavingAccount`.`SavingAccountNumber`=?";
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet rs = null;
		try {
			connection = dataSource.getConnection();
			preparedStatement = connection.prepareStatement(query);
			preparedStatement.setLong(1, customerAccountID);
			rs = preparedStatement.executeQuery();
			if (rs.next()) {
				if (AccountTransactionState.ACTIVE.getState().equals(rs
								.getString("AccountTransactionState")))
					return true;
			}
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			DBUtils.closeResultSetConnection(connection, preparedStatement, rs,
					logger);
		}
		return false;
	}

	@Override
	public List<CustomerAccount> getAccountByState(String accountState) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public List<CustomerAccount> getAccountByIDCardNumber(String idCardNumber) {
		List<CustomerAccount> resultList = new ArrayList<CustomerAccount>();
		String selectCustIDQR = "SELECT * FROM `Customer` WHERE `IDCardNumber`=?";
		String selectAccount = "SELECT * FROM `SavingAccount` WHERE `Customer_CustomerID`=?";
		Connection conn = null;
		PreparedStatement preparedStatement = null;
		PreparedStatement preparedStatement2 = null;
		try {

			conn = dataSource.getConnection();
			preparedStatement = conn.prepareStatement(selectCustIDQR);
			preparedStatement.setString(1, idCardNumber);
			ResultSet resultCustIDRS = preparedStatement.executeQuery();

			String customerID = "";

			while (resultCustIDRS.next()) {
				customerID = resultCustIDRS.getString("CustomerID");

				preparedStatement2 = conn.prepareStatement(selectAccount);
				preparedStatement2.setString(1, customerID);
				ResultSet selectAccountRS = preparedStatement2.executeQuery();

				while (selectAccountRS.next()) {
					SavingAccount savingAccount = new SavingAccount();
					savingAccount.setAccountNumber(selectAccountRS
							.getLong("SavingAccountNumber"));
					savingAccount.setAccountType(selectAccountRS
							.getByte("AccountType"));
					savingAccount.setAccountBalance(selectAccountRS
							.getDouble("AccountBalance"));
					savingAccount.setRepeatable(selectAccountRS
							.getBoolean("isRepeatable"));
					savingAccount.setCreatedDate(selectAccountRS
							.getTimestamp("CreatedDate"));
					savingAccount.setAccountState(AccountState
							.valueOf(selectAccountRS.getString("AccountState")
									.toUpperCase()));
					savingAccount.setCustomerID(selectAccountRS
							.getLong("Customer_CustomerID"));
					resultList.add(savingAccount);
				}
			}
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				logger.error(e);
				e.printStackTrace();
			}
		}
		return resultList;
	}

	@Override
	public List<CustomerAccount> getAccountByFirstName(String firstName) {
		List<CustomerAccount> resultList = new ArrayList<CustomerAccount>();
		String selectCustIDQR = "SELECT * FROM `Customer` WHERE `FirstName` LIKE '%"
				+ firstName + "%'";
		String selectAccount = "SELECT * FROM `SavingAccount` WHERE `Customer_CustomerID`=?";
		Connection conn = null;
		PreparedStatement preparedStatement = null;
		PreparedStatement preparedStatement2 = null;
		try {

			conn = dataSource.getConnection();
			preparedStatement = conn.prepareStatement(selectCustIDQR);
			ResultSet resultCustIDRS = preparedStatement.executeQuery();

			String customerID = "";

			while (resultCustIDRS.next()) {
				customerID = resultCustIDRS.getString("Customer_CustomerID");

				preparedStatement2 = conn.prepareStatement(selectAccount);
				preparedStatement2.setString(1, customerID);
				ResultSet selectAccountRS = preparedStatement2.executeQuery();

				while (selectAccountRS.next()) {
					SavingAccount savingAccount = new SavingAccount();
					savingAccount.setAccountNumber(selectAccountRS
							.getLong("SavingAccountNumber"));
					savingAccount.setAccountType(selectAccountRS
							.getByte("AccountType"));
					savingAccount.setAccountBalance(selectAccountRS
							.getDouble("AccountBalance"));
					savingAccount.setRepeatable(selectAccountRS
							.getBoolean("isRepeatable"));
					savingAccount.setCreatedDate(selectAccountRS
							.getTimestamp("CreatedDate"));
					savingAccount.setAccountState(AccountState
							.valueOf(selectAccountRS.getString("AccountState")
									.toUpperCase()));
					savingAccount.setCustomerID(selectAccountRS
							.getLong("Customer_CustomerID"));
					resultList.add(savingAccount);
				}
			}
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				logger.error(e);
				e.printStackTrace();
			}
		}
		return resultList;
	}

	@Override
	public List<CustomerAccount> getAccoungByPhoneNumber(String phoneNumber) {
		List<CustomerAccount> resultList = new ArrayList<CustomerAccount>();
		String selectCustIDQR = "SELECT * FROM `Customer` WHERE `PhoneNumber` LIKE '%"
				+ phoneNumber + "%'";
		String selectAccount = "SELECT * FROM `SavingAccount` WHERE `Customer_CustomerID`=?";
		Connection conn = null;
		PreparedStatement preparedStatement = null;
		PreparedStatement preparedStatement2 = null;
		try {

			conn = dataSource.getConnection();
			preparedStatement = conn.prepareStatement(selectCustIDQR);
			ResultSet resultCustIDRS = preparedStatement.executeQuery();

			String customerID = "";

			while (resultCustIDRS.next()) {
				customerID = resultCustIDRS.getString("CustomerID");

				preparedStatement2 = conn.prepareStatement(selectAccount);
				preparedStatement2.setString(1, customerID);
				ResultSet selectAccountRS = preparedStatement2.executeQuery();

				while (selectAccountRS.next()) {
					SavingAccount savingAccount = new SavingAccount();
					savingAccount.setAccountNumber(selectAccountRS
							.getLong("SavingAccountNumber"));
					savingAccount.setAccountType(selectAccountRS
							.getByte("AccountType"));
					savingAccount.setAccountBalance(selectAccountRS
							.getDouble("AccountBalance"));
					savingAccount.setRepeatable(selectAccountRS
							.getBoolean("isRepeatable"));
					savingAccount.setCreatedDate(selectAccountRS
							.getTimestamp("CreatedDate"));
					savingAccount.setAccountState(AccountState
							.valueOf(selectAccountRS.getString("AccountState")
									.toUpperCase()));
					savingAccount.setCustomerID(selectAccountRS
							.getLong("Customer_CustomerID"));
					resultList.add(savingAccount);
				}
			}
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				logger.error(e);
				e.printStackTrace();
			}
		}
		return resultList;
	}

	@Override
	public List<CustomerAccount> getAccountByAddress(String address) {
		List<CustomerAccount> resultList = new ArrayList<CustomerAccount>();
		String selectCustIDQR = "SELECT * FROM `Customer` WHERE `Address` LIKE '%"
				+ address + "%'";
		String selectAccount = "SELECT * FROM `SavingAccount` WHERE `Customer_CustomerID`=?";
		Connection conn = null;
		PreparedStatement preparedStatement = null;
		PreparedStatement preparedStatement2 = null;
		try {

			conn = dataSource.getConnection();
			preparedStatement = conn.prepareStatement(selectCustIDQR);
			ResultSet resultCustIDRS = preparedStatement.executeQuery();

			String customerID = "";

			while (resultCustIDRS.next()) {
				customerID = resultCustIDRS.getString("CustomerID");

				preparedStatement2 = conn.prepareStatement(selectAccount);
				preparedStatement2.setString(1, customerID);
				ResultSet selectAccountRS = preparedStatement2.executeQuery();

				while (selectAccountRS.next()) {
					SavingAccount savingAccount = new SavingAccount();
					savingAccount.setAccountNumber(selectAccountRS
							.getLong("SavingAccountNumber"));
					savingAccount.setAccountType(selectAccountRS
							.getByte("AccountType"));
					savingAccount.setAccountBalance(selectAccountRS
							.getDouble("AccountBalance"));
					savingAccount.setRepeatable(selectAccountRS
							.getBoolean("isRepeatable"));
					savingAccount.setCreatedDate(selectAccountRS
							.getTimestamp("CreatedDate"));
					savingAccount.setAccountState(AccountState
							.valueOf(selectAccountRS.getString("AccountState")
									.toUpperCase()));
					savingAccount.setCustomerID(selectAccountRS
							.getLong("Customer_CustomerID"));
					resultList.add(savingAccount);
				}
			}
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				logger.error(e);
				e.printStackTrace();
			}
		}
		return resultList;
	}

	public static void main(String[] agrs) {
		SavingAccountDao savAccountDao = new SavingAccountDaoImpl();
		List<CustomerAccount> resultList = savAccountDao
				.getAccountByAddress("13");

		for (int i = 0; i < resultList.size(); i++) {
			SavingAccount savAccount = (SavingAccount) resultList.get(i);
			System.out.println(savAccount.getCustomerID());
		}
	}

	@Override
	public CustomerAccount getCustomerAccountByID(long cutomerAccountID) {
		String query = "SELECT * FROM `SavingAccount` WHERE `SavingAccount`.`SavingAccountNumber`=?";
		SavingAccount savingAccount = null;
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet rs = null;
		try {
			connection = dataSource.getConnection();
			preparedStatement = connection.prepareStatement(query);
			preparedStatement.setLong(1, cutomerAccountID);
			rs = preparedStatement.executeQuery();
			if (rs.next())
				savingAccount = getSavingAccountResultSet(rs);
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			DBUtils.closeResultSetConnection(connection, preparedStatement, rs,
					logger);
		}
		return savingAccount;
	}

	@Override
	public boolean updateStateToActive(SavingAccount savingAccount) {
		String query = "UPDATE `SavingAccount` "
				+ " SET  `AccountState`='active' "
				+ "WHERE `SavingAccount`.`SavingAccountNumber`=? ";

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = dataSource.getConnection();
			connection.setAutoCommit(false);
			preparedStatement = connection.prepareStatement(query);
			preparedStatement.setLong(1, savingAccount.getAccountNumber());
			preparedStatement.execute();
			connection.commit();
			return true;
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
			try {
				connection.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
				logger.error(e1);
			}
		} finally {
			DBUtils.closeConnection(connection, preparedStatement, logger);
		}
		return false;

	}

	@Override
	public boolean approveAccount(SavingAccount savingAccount) {
		String query = "UPDATE `SavingAccount` "
				+ " SET  `AccountState`='active', `AccountTransactionState`='active' "
				+ "WHERE `SavingAccount`.`SavingAccountNumber`=? ";

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = dataSource.getConnection();
			connection.setAutoCommit(false);
			preparedStatement = connection.prepareStatement(query);
			preparedStatement.setLong(1, savingAccount.getAccountNumber());
			preparedStatement.execute();
			connection.commit();
			return true;
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
			try {
				connection.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
				logger.error(e1);
			}
		} finally {
			DBUtils.closeConnection(connection, preparedStatement, logger);
		}
		return false;

	}

	@Override
	public boolean updateStateToDisable(SavingAccount savingAccount) {
		String query = "UPDATE `SavingAccount` "
				+ " SET  `AccountState`='disable' "
				+ "WHERE `SavingAccount`.`SavingAccountNumber`=? ";

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = dataSource.getConnection();
			connection.setAutoCommit(false);
			preparedStatement = connection.prepareStatement(query);
			preparedStatement.setLong(1, savingAccount.getAccountNumber());
			preparedStatement.execute();
			connection.commit();
			return true;
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
			try {
				connection.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
				logger.error(e1);
			}
		} finally {
			DBUtils.closeConnection(connection, preparedStatement, logger);
		}
		return false;
	}

	@Override
	public boolean updateStateToRemovable(SavingAccount savingAccount) {
		String query = "UPDATE `SavingAccount` "
				+ " SET  `AccountState`='removable' "
				+ "WHERE `SavingAccount`.`SavingAccountNumber`=? ";

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = dataSource.getConnection();
			connection.setAutoCommit(false);
			preparedStatement = connection.prepareStatement(query);
			preparedStatement.setLong(1, savingAccount.getAccountNumber());
			preparedStatement.execute();
			connection.commit();
			return true;
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
			try {
				connection.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
				logger.error(e1);
			}
		} finally {
			DBUtils.closeConnection(connection, preparedStatement, logger);
		}
		return false;
	}

	@Override
	public boolean updateStateToRemoved(SavingAccount savingAccount) {
		String query = "UPDATE `SavingAccount` "
				+ " SET  `AccountState`='removed' "
				+ "WHERE `SavingAccount`.`SavingAccountNumber`=? ";

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = dataSource.getConnection();
			connection.setAutoCommit(false);
			preparedStatement = connection.prepareStatement(query);
			preparedStatement.setLong(1, savingAccount.getAccountNumber());
			preparedStatement.execute();
			connection.commit();
			return true;
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
			try {
				connection.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
				logger.error(e1);
			}
		} finally {
			DBUtils.closeConnection(connection, preparedStatement, logger);
		}
		return false;
	}

	@Override
	public List<CustomerAccount> getAllSavingAccounts() {
		List<CustomerAccount> customerAccounts = new ArrayList<CustomerAccount>();
		String query = "SELECT * FROM `savingaccount` LEFT JOIN `customer`"
				+ " ON `savingaccount`.`Customer_CustomerID` = `customer`.`CustomerID`"
				+ " ORDER BY `savingaccount`.`CreatedDate` DESC";
		SavingAccount savingAccount = null;
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet rs = null;
		try {
			connection = dataSource.getConnection();
			rs = DatabaseUtil.executeQuery(preparedStatement, connection,
					query, null);
			if (rs != null) {
				while (rs.next()) {
					savingAccount = getSavingAccountResultSet(rs);
					customerAccounts.add(savingAccount);
				}
			}
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			DatabaseUtil.closeConnection(connection, preparedStatement, logger);
		}
		return customerAccounts;
	}

	@Override
	public List<CustomerAccount> getAllIsRepeatableSavingAccountByDate(
			Timestamp today) {
		String state = "accepted";
/*		Calendar cal = Calendar.getInstance();
		Date toDay = new Date(System.currentTimeMillis());

		cal.setTime(toDay);
		cal.add(Calendar.DATE, 1); // add days
		toDay = cal.getTime();
		today = new Timestamp(toDay.getTime());*/
		
		
		List<CustomerAccount> customerAccounts = new ArrayList<CustomerAccount>();
		String query = "SELECT * FROM `savingaccount`,`SavingAccount_has_InterestRate`"
				+ " WHERE `savingaccount`.`SavingAccountNumber` = `SavingAccount_has_InterestRate`.`SavingAccount_AccNumber`"
				+ " AND `savingaccount`.`isRepeatable` = 1"
				+ " AND `SavingAccount_has_InterestRate`.`State` = ?"
				+ " AND date(`SavingAccount_has_InterestRate`.`ToDate`) = current_date";
		SavingAccount savingAccount = null;
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet rs = null;
		try {
			connection = dataSource.getConnection();
			rs = DatabaseUtil.executeQuery(preparedStatement, connection,
					query, new Object[] { state });
			if (rs != null) {
				while (rs.next()) {
					savingAccount = getSavingAccountResultSet(rs);
					customerAccounts.add(savingAccount);
				}
			}
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			DatabaseUtil.closeConnection(connection, preparedStatement, logger);
		}
		return customerAccounts;
	}

	@Override
	public boolean repeatPeriod(SavingAccount savingAccount) {
		double interest = 0;
		int days = 0;
		InterestRate interestRate = null;
		InterestRate newestInterest = null;
		AccountHasInterestRate accountHasInterestRate = null;
		PeriodType periodType = null;
		double currentBalance = 0;
		if (savingAccount != null) {

			// Calculate interest

			accountHasInterestRate = accountHasInterestRateDao
					.getInterestRateByAccountNumberID(savingAccount
							.getAccountNumber());
			if (accountHasInterestRate != null) {
				interestRate = interestRateService
						.getInterestRateByID(accountHasInterestRate
								.getInterestRateID());
				if (interestRate != null) {
					periodType = periodTypeDao.getPeriodTypeByID(interestRate
							.getPeriodTypeID());
					if (periodType.getSavingPeriod() > 0) {
						if (periodType.getUnit().equals("week")) {
							days = periodType.getSavingPeriod() * 7;
							interest = savingAccount.getAccountBalance()
									* (((interestRate.getRate() / 100) / 12 / 30) * 7 * periodType
											.getSavingPeriod());
						} else if (periodType.getUnit().equals("month")) {
							days = periodType.getSavingPeriod() * 30;
							interest = savingAccount.getAccountBalance()
									* (((interestRate.getRate() / 100) / 12) * periodType
											.getSavingPeriod());
						}
					} else {
						interest = savingAccount.getAccountBalance()
								* ((interestRate.getRate() / 12 / 30));
					}
					System.out.println("Interest: " + interest);
					currentBalance = savingAccount.getAccountBalance();
					System.out.println("Current Balance: "
							+ savingAccount.getAccountBalance());
					savingAccount.setAccountBalance(savingAccount
							.getAccountBalance() + interest);
					// Get newest rate
					newestInterest = interestRateService
							.getNewestInterestRateByCurrencyPeriod(
									interestRate.getCurrencyID(),
									interestRate.getPeriodTypeID());
					
					
					Calendar cal = Calendar.getInstance(); 
					Date from = new Date(accountHasInterestRate
							.getToDate().getTime());
					cal.setTime(from);  
					cal.add(Calendar.DATE, 1); // add 1 day
					from = cal.getTime(); 
					Timestamp fromDate = new Timestamp(from.getTime());
					
					Date to = new Date(fromDate.getTime());
					cal.setTime(to);  
					cal.add(Calendar.DATE, days); // add days  
					to = cal.getTime();
					Timestamp toDate = new Timestamp(to.getTime());

					SavingAccountHasInterestRate newSavingBook = new SavingAccountHasInterestRate(
							savingAccount.getAccountNumber(),
							newestInterest.getInterestRateID(), fromDate,
							toDate, savingAccount.getAccountBalance(),
							AccountHasInterestRateState.ACCEPTED);
					
					//Create transaction
					
					SavingTransaction savingTransaction = new SavingTransaction(interest, currentBalance, 40029, 40029, SavingTransactionType.REPEAT_PERIOD, savingAccount.getAccountNumber());
					savingTransaction.setTransactionState(TransactionState.DONE);
					
					// Update balance & Create new saving book
					if (updateRepeatable(savingAccount, newSavingBook,
							accountHasInterestRate.getId(), savingTransaction)) {
						return true;
					}
				}
			}
		}
		return false;
	}

}
